﻿
CREATE PROCEDURE [dbo].[GenListaOperatiuni]   
@idLista smallint ,
@CodUnitate smallint
AS


DECLARE @strCapitol varchar(100)
DECLARE @strFNI varchar(100)
DECLARE @strFiltruCapitol varchar(100)
DECLARE @strArticol varchar(100)
DECLARE @strSelect varchar(8000)
DECLARE @strSelect2 varchar(8000)
DECLARE @strFiltru varchar(8000)
DECLARE @strFiltru2 varchar(8000)
DECLARE @idJurn int
DECLARE @fni bit
SET @fni=0
DECLARE @dataUI as float, @dtDataStartUI as datetime,@dtDataEndUI as datetime
DECLARE @strContStart as TipCont,@strContEnd as TipCont
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @tabela varchar(100)


SET @CodUnitate=0
SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()
--PRINT('CodUnitate ''' + cast(@CodUnitate as varchar(20)) + '''')

SELECT @strDataStart=Valoare FROM [ParamListe] WHERE [IDLista]=@idLista AND Cod='DLD' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
SET @strDataStart = RTRIM(LTRIM(@strDataStart))
IF LEN(@strDataStart)<>0
BEGIN
	SET @dtDataStartUI = CAST(CAST(@strDataStart AS float) AS datetime)
	SET @strDataStart = ''''+CAST(@dtDataStartUI AS varchar(50))+''''
END

SELECT @strDataEnd=Valoare FROM [ParamListe] WHERE [IDLista]=@idLista AND Cod='PLD' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate] = @CodUnitate
SET @strDataEnd = RTRIM(LTRIM(@strDataEnd))

IF LEN(@strDataStart)<>0
BEGIN
	SET @dtDataEndUI = CAST(CAST(@strDataEnd AS float) AS datetime)
	SET @strDataEnd = ''''+CAST(@dtDataEndUI AS varchar(50))+''''
END


SELECT @strCapitol=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='JRN' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate
--PRINT('[' + @strCapitol + ']')
SELECT @strFNI=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='FNI' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate
if @strFNI = '1'
	SET @fni = 1
SELECT @strArticol=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='AAA' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate
SELECT @idJurn = IDJurnal FROM [Jurnale] WHERE [Codificare]=@strCapitol  AND [CodUnitate]=@CodUnitate

--Print(@strArticol)
if @strArticol='' OR @strArticol is NULL
	GOTO EROARE1
SET @strFiltruCapitol = ''
if @idJurn<>0 AND @idJurn is NOT NULL
	SET @strFiltruCapitol = ' AND IDJurnal='+cast(@idJurn as varchar(10))


DECLARE @det varchar(1000)
DECLARE @plati varchar(1000)
DECLARE @chelt varchar(2000)
DECLARE @aprobate varchar(2000)
DECLARE @toate varchar(6000)

SET @det =','
SET @plati =','
SET @chelt =','
SET @aprobate =','

SELECT @det=@det + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CD}%' AND [CodUnitate]=@CodUnitate
SELECT @aprobate=@aprobate + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CA}%'  AND [CodUnitate]=@CodUnitate
SELECT @plati=@plati + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '7%'  AND [CodUnitate]=@CodUnitate
SELECT @chelt=@chelt +  Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '6%'  AND [CodUnitate]=@CodUnitate
SET @toate = @det+@chelt+@plati+@aprobate


CREATE TABLE [#doc_aa] (
	[Data] datetime NOT NULL, 
	[Numar] decimal(20,0) NOT NULL DEFAULT 0,
	[Explicatii] varchar(200) NOT NULL,	
	[ValoareLei] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[ContDebit] varchar(50) NOT NULL,
	[ContCredit] varchar(50) NOT NULL,	
	
	[CrediteAprobate] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CrediteDeschise] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[Plati] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[Restituiri] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[CheltuieliDebit] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CheltuieliCredit] DECIMAL(20,2) NOT NULL DEFAULT 0

)


SET @strSelect ='INSERT INTO [#doc_aa]
SELECT  rd.DataDoc, rd.NumarDoc, r.ExplicatiiDocJust, r.ValoareLei,dbo.GetSintetic(r.ContDebit),dbo.GetSintetic(r.ContCredit),
dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@aprobate+''', r.ValoareLei) - dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit) +'','','''+@aprobate+''', r.ValoareLei),
dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@det+''', r.ValoareLei) - dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit) +'','','''+@det+''', r.ValoareLei),'+
'dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit)+'','','''+@plati+''', r.ValoareLei) , dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit) +'','','''+@plati+''', r.ValoareLei),
dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@chelt+''', r.ValoareLei) , dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit) +'','','''+@chelt+''', r.ValoareLei)
FROM [Rulaje] r JOIN [RulajeDoc] rd ON r.IDDocument=rd.IDDocument '

SET @strSelect2 ='INSERT INTO [#doc_aa]
SELECT  ''1900-01-01'', 0, '''', coalesce(sum(r.ValoareLei),0) AS [ValoareLei],'''','''',
Coalesce(sum(dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@aprobate+''', r.ValoareLei) - dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit) +'','','''+@aprobate+''', r.ValoareLei)),0),
coalesce(sum(dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@det+''', r.ValoareLei) - dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit) +'','','''+@det+''', r.ValoareLei)),0),
coalesce(sum(dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit)+'','','''+@plati+''', r.ValoareLei)),0) , coalesce(sum(dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit) +'','','''+@plati+''', r.ValoareLei)),0),
coalesce(sum(dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@chelt+''', r.ValoareLei)),0) , coalesce(sum(dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit) +'','','''+@chelt+''', r.ValoareLei)),0)
FROM [Rulaje] r JOIN [RulajeDoc] rd ON r.IDDocument=rd.IDDocument '



SET @strFiltru = 'WHERE rd.[CodUnitate]='+CAST(@CodUnitate AS varchar(20)) +' AND
(dbo.GetArticolAliniat(r.ContDebit) like '''+@strArticol+'%''  OR dbo.GetArticolAliniat(r.ContCredit) like '''+@strArticol+'%'') AND
(dbo.IsContIn('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@toate+''')<>0  OR dbo.IsContIn('',''+ dbo.GetSintetic(r.ContCredit)+'','','''+@toate+''')<>0) '
IF @fni = 1
	SET @strFiltru =  @strFiltru + '  AND r.TipDocJust <>''IC'' '
IF @strFiltruCapitol<>''
	SET @strFiltru =  @strFiltru +@strFiltruCapitol
	

SET @strFiltru2 = @strFiltru

IF LEN(@strDataStart) <>0
	SET @strFiltru = @strFiltru + ' AND rd.DataDoc >='+@strDataStart
IF LEN(@strDataEnd) <>0
	SET @strFiltru = @strFiltru + ' AND rd.DataDoc <='+@strDataEnd	
SET @strFiltru =  @strFiltru +' ORDER BY rd.DataDoc'

IF LEN(@strDataStart) <>0
	SET @strFiltru2 = @strFiltru2 + ' AND rd.DataDoc <'+@strDataStart



SET @strSelect = @strSelect + LTRIM(RTRIM(@strFiltru))
SET @strSelect2 = @strSelect2 + LTRIM(RTRIM(@strFiltru2))
PRINT(@strSelect)
EXEC(@strSelect)
IF @@ERROR != 0 GOTO EROARE				
PRINT(@strSelect2)
EXEC(@strSelect2)
IF @@ERROR != 0 GOTO EROARE				


SELECT	[Data],[Numar],[Explicatii],[CrediteAprobate],[CrediteDeschise],[Plati],[Restituiri],[CheltuieliDebit],[CheltuieliCredit]
FROM  [#doc_aa]
ORDER BY [Data]

GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea listei',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('Va rugam sa selectati articolul si aliniatul pentru lista',16,-1)
	GOTO Gata
EROARE2:
	RAISERROR ('Capitol incorect',16,-1)
	GOTO Gata

Gata: